-module(erlydb_base).

-define(L(Obj), io:format("LOG ~w ~p\n", [?LINE, Obj])).
-define(S(Obj), io:format("LOG ~w ~s\n", [?LINE, Obj])).

%-compile(export_all).

-export(
   [fields/1,
    new/1,
    save/2,
    delete/1,
    find/2,
    find_where/2,
    find_where/3,
    find_id/2,
    find_all/1,
    get_related_many_to_one/2,
    get_related_many_to_one/3,
    get_related_many_to_one/4,
    get_related_many_to_one_with/3,
    get_related_one_to_many/2,
    set_related_one/2,
    set_related_one_to_many/2,
    get_related_many_to_many/3,
    get_related_many_to_many/4,
    get_related_many_to_many/5,
    get_related_many_to_many_with/4,
    add_related_many_to_many/3,
    remove_related_many_to_many/3
   ]).

%% A metafunction for getting the fields for the module
fields(Fields) ->
    Fields.

get_module(Obj) ->
    element(2, Obj).

set_module(Obj, Module) ->
    setelement(2, Obj, Module).

get_module_str(Obj) ->
    atom_to_list(element(2, Obj)).

get_id(Obj) ->
    element(3, Obj).

get_id_str(Obj) ->
    integer_to_list(element(3,Obj)).

set_id(Obj, Id) ->
    setelement(3, Obj, Id).

get_is_new(Obj) ->
    element(1, Obj).

set_is_new(Obj, Val) ->
    setelement(1, Obj, Val).
    
%% Create a new object for for given Module name and number of fields.
%%
%% @spec new(Module::atom(), NumFields::integer()) -> tuple()
new(Module) ->
    Obj = erlang:make_tuple(length(Module:fields()) + 2, undefined),
    Obj1 = set_is_new(Obj, true),
    set_module(Obj1, Module).

%% @doc Save an object by executing a INSERT or UPDATE query.
%%
%% @spec save(Module::atom() | {update, Sql::string()},
%%   Obj::tuple()) -> {ok, NewObj::tuple()} | {error, Err}
save(Module, Obj) ->
    case make_save_query(Module:fields(), Obj) of
	{insert, Sql} ->
	    case erlydb_drv:update(Sql) of
		{ok, 1} ->
		    case erlydb_drv:get_last_insert_id() of
			{ok, Id} ->
			    Obj1 = set_is_new(Obj, false),
			    {ok, set_id(Obj1, Id)};
			Err ->
			    Err
		    end;	    
		Err ->
		    Err
	    end;
	{update, Sql} ->
	    case erlydb_drv:update(Sql) of
		%% TODO figure out why the MySQL driver sometimes reports that
		%% no rows were updated
		{ok, 0} ->

		    %% UPDATE queries currently don't change the object
		    %% but it's good to keep uniform semantics in case
		    %% this changes.
		    {ok, Obj}; 
		{ok, 1} ->
		    {ok, Obj};
		Err ->
		    Err
	    end
    end.

%% @doc Construct an INSERT or UPDATE query for the given Object and list
%%   of fields.
%%
%% @spec make_save_query(Fields::[atom()], Obj::tuple()) ->
%%   {update, Sql::string()} | {insert, Sql::string()}
make_save_query([_IdField | Fields], Obj) ->
    case get_is_new(Obj) of
	false ->
	    [_IsNew, Table, Id | Vals] = tuple_to_list(Obj),
	    S1 = " WHERE id=" ++ integer_to_list(Id),
	    {S3, _} = 
		lists:foldl(
		  fun({Field, Val}, {S2, false}) ->
			  {Field ++ [$= | encode(Val) ++ S2],
			   true};
		     ({Field, Val}, {S2, true}) ->
			  {Field ++ [$= | encode(Val) ++
				     [$,|S2]], true}
		  end, {S1, false},
		  lists:zip(
		    lists:map(fun atom_to_list/1, Fields), Vals)),
	    {update, lists:append(
	      ["UPDATE ",
	       atom_to_list(Table),
	       " SET ", S3])};
	true ->
	    [_IsNew, _Table, _Id | Vals] = tuple_to_list(Obj),
	    S1 = prepend_list(
		   lists:map(fun encode/1, Vals),")"),
	    {insert,
	     lists:append(
	       ["INSERT INTO ", get_module_str(Obj),
		" (", prepend_list(
			lists:map(fun atom_to_list/1, Fields),
			") VALUES ("), S1])}
    end.

%% @doc Execute a DELETE statement for the given object
%%
%% @spec delete(Obj::tuple()) -> ok | {error, Error}
delete(Obj) ->
    case make_delete_query(Obj) of
	{ok, Query} ->
	    case erlydb_drv:update(Query) of
		{ok, 1} ->
		    ok;
		{ok, 0} ->
		    {error, delete_failed};
		{ok, Num} ->
		    {error, {too_many_rows_deleted, Num}}
	    end;
	Err ->
	    Err
    end.

%% @doc Construct a SQL DELETE statement for the given object.
%%
%% @spec make_delete_query(Obj::tuple()) ->
%%   {ok, Sql::string()} | {error, no_such_record}
make_delete_query(Obj) ->
    case get_is_new(Obj) of
	false ->
	    {ok, lists:append(
	      ["DELETE FROM ",
	       get_module_str(Obj),
	       " WHERE id=",
	       get_id_str(Obj)])};
	true ->
	    {error, no_such_record}
    end.

%% @doc Execute a SELECT statement for the given module and unique id.
%%
%% @spec find_id(Module::atom(), Id::integer()) ->
%%   {ok, Obj::tuple()} | {error, Err}
find_id(Module, Id) ->
    case erlydb_drv:select(
	   lists:append(
	     ["SELECT * FROM ", atom_to_list(Module), " WHERE id=",
	      integer_to_list(Id)]), Module) of
	{ok, []} ->
	    {error, no_such_record};
	{ok, [Rec]} ->
	    {ok, Rec};
	{ok, [_Rec | _Rest]} ->
	    {error, too_many_records};
	Err ->
	    Err
    end.



%% @doc Execute a SELECT statement for the given module with the given
%%   WHERE clause and extra clause (e.g. "LIMIT 1").
%%
%% @spec find_where(Module::atom(), WhereClause::string()) ->
%%   {ok, [Row::tuple()]} | {error, Err}
find_where(Module, WhereClause) ->
    find_where(Module, WhereClause, "").

find_where(Module, WhereClause, ExtraClause) ->
    do_select(
      ["SELECT * FROM ",
       atom_to_list(Module), " WHERE (", WhereClause, ") ", ExtraClause],
      Module).


%% @doc Execute a SELECT statement for the give module with the
%%  user-provided postfix.
%%
%% @spec find(Module::atom(), Sql::string()) ->
%%   {ok, [Row::tuple()]} | {error, Err}
find(Module, Sql) ->
    do_select(
      ["SELECT * FROM ",
      atom_to_list(Module),
       " ", Sql], Module).

%% @doc Execute a SELECT statement that returns all records for
%%   the given module.
%%
%% @spec find_sqll(Module::atom()) ->
%%   {ok, [Row::tuple()]} | {error, Err}
find_all(Module) ->
    do_select(["SELECT * FROM ", atom_to_list(Module)], Module).

do_select(QueryFrags, Module) ->
    erlydb_drv:select(lists:append(QueryFrags), Module).

set_related_one(Obj, Other) ->
    case verify_saved(Other) of
	ok ->
	    FuncName = other_id_func_name(Other),
	    Module = get_module(Obj),
	    Module:FuncName(Obj, get_id(Other));
	Err ->
	    Err
    end.

get_related_one_to_many(OtherModule, Obj) ->
    OtherTableStr = atom_to_list(OtherModule),
    FuncName = list_to_atom(OtherTableStr ++ "_id"),
    ModName = get_module(Obj),
    Query =
	lists:append(
	  ["SELECT * FROM ",
	   OtherTableStr,
	   " WHERE id=",
	   integer_to_list(ModName:FuncName(Obj))]),
    case erlydb_drv:select(Query, OtherModule) of
	{ok, [Res]} ->
	    {ok, Res};
	{ok, [_|_]} ->
	    {error, {too_many_results, Query}};
	Err ->
	    Err
    end.

%% e.g. project:language(Proj, Erlang)
set_related_one_to_many(Obj, OtherObj) ->
    set_related_one(Obj, OtherObj).


get_related_many_to_one(OtherModule, Obj) ->
    get_related_many_to_one(OtherModule, Obj, undefined).

get_related_many_to_one_with(OtherModule, Obj, ExtraClause) ->
    get_related_many_to_one(OtherModule, Obj, undefined, ExtraClause).

get_related_many_to_one(OtherModule, Obj, WhereClause) ->
    get_related_many_to_one(OtherModule, Obj, WhereClause, "").

%% @doc Retrieve related records from the database by executing a
%%   SELECT statement.
%%
%% @spec get_related_many(Module::atom(), Other::atom(), Obj::tuple()
%%     WhereClause::string(), ExtraClause::string()) ->
%%   {ok, Rows} | {error, Error}
get_related_many_to_one(OtherModule, Obj, WhereClause, ExtraClause) ->
    case verify_saved(Obj) of
	ok ->
	    Query = 
		make_get_related_many_to_one_query(
		  OtherModule, Obj, WhereClause,
		  ExtraClause),
	    erlydb_drv:select(Query, OtherModule);
	Err ->
	    Err
    end.

make_get_related_many_to_one_query(OtherModule, Obj, WhereClause,
				   ExtraClause) ->
    WhereClause1 =
	make_nested_where_clause(WhereClause),

    lists:append(
      ["SELECT * FROM ",
       atom_to_list(OtherModule), " WHERE ",
       get_module_str(Obj), "_id=",
       get_id_str(Obj), WhereClause1, ExtraClause]).

other_id_func_name(Other) when is_tuple(Other) ->
    other_id_func_name(get_module(Other));
other_id_func_name(Module) ->
    list_to_atom(
      atom_to_list(Module) ++ "_id").

get_related_many_to_many(OtherModule, JoinTable, Obj) ->
    get_related_many_to_many(OtherModule, JoinTable, Obj, undefined).

get_related_many_to_many_with(OtherModule, JoinTable, Obj, ExtraClause) ->
    get_related_many_to_many(OtherModule, JoinTable, Obj, undefined, ExtraClause).

get_related_many_to_many(OtherModule, JoinTable, Obj, WhereClause) ->
    get_related_many_to_many(OtherModule, JoinTable, Obj, WhereClause, "").

get_related_many_to_many(OtherModule, JoinTable, Obj, WhereClause, ExtraClause) ->
    case verify_saved(Obj) of
        ok ->
	    Query = make_get_related_many_to_many_query(
		      OtherModule, JoinTable, Obj, WhereClause,
		      ExtraClause),
	    erlydb_drv:select(Query, OtherModule);
	Err ->
	    Err
    end.

make_get_related_many_to_many_query(OtherModule, JoinTable, Obj, WhereClause,
				    ExtraClause) ->
    OtherTableStr = atom_to_list(OtherModule),
    ThisTableStr = get_module_str(Obj),
    JoinTableStr = atom_to_list(JoinTable),
    WhereClause1 = make_nested_where_clause(WhereClause),
    FieldStrs = make_str_for_fields(OtherModule),
    Query =
	lists:append(
	  ["SELECT ", FieldStrs, " FROM ",
	   OtherTableStr, ",", JoinTableStr,
	   " WHERE (", OtherTableStr,
	   ".id = ", JoinTableStr,
	   ".", OtherTableStr, "_id AND ",
	   JoinTableStr, ".", ThisTableStr, "_id=",
	   get_id_str(Obj), ")", WhereClause1, ExtraClause]),
    Query.

make_str_for_fields(Module) ->
    TableStr = atom_to_list(Module),
    {Result, _} =
	lists:foldl(
	  fun(Field, {Str, IsFirst}) ->
		  FieldName = TableStr ++ "." ++ atom_to_list(Field),
		  Str1 =
		      if 
			  IsFirst ->
			      FieldName;
			  true ->
			      FieldName ++ ","
		      end,
		  {Str1 ++ Str, false}
	  end, {"", true}, lists:reverse(Module:fields())),
    Result.  

make_nested_where_clause(WhereClause) ->
    case WhereClause of
	undefined ->
	    " ";
	_ ->
	    " AND (" ++ WhereClause ++ ") "
    end.
    

add_related_many_to_many(JoinTable, Obj, OtherObj) ->
    case verify_saved(Obj, OtherObj) of
	ok ->
	    Query = make_add_related_many_to_many_query(
		      JoinTable, Obj, OtherObj),
	    case erlydb_drv:update(Query) of
		{ok, _Num} ->
		    ok;
		Err ->
		    Err
	    end;
	Err ->
	    Err
    end.

make_add_related_many_to_many_query(JoinTable, Obj, OtherObj) ->
    {JoinTableStr, ObjIdField, OtherIdField} =
	get_many_to_many_query_strs(JoinTable, Obj, OtherObj),
    lists:append(
      ["INSERT INTO ", JoinTableStr, " (",
       ObjIdField, ",", OtherIdField,") VALUES (",
       get_id_str(Obj), ",", get_id_str(OtherObj), ")"]).

remove_related_many_to_many(JoinTable, Obj, OtherObj) ->
    case verify_saved(Obj, OtherObj) of
	ok ->
	    Query = make_remove_related_many_to_many_query(
		      JoinTable, Obj, OtherObj),
	    case erlydb_drv:update(Query) of
		{ok, _Num} ->
		    ok;
		Err ->
		    Err
	    end;
	Err ->
	    Err
    end.

make_remove_related_many_to_many_query(JoinTable, Obj, OtherObj) ->
    {JoinTableStr, ObjIdField, OtherIdField} =
	get_many_to_many_query_strs(JoinTable,Obj,OtherObj),
    lists:append(
      ["DELETE FROM ", JoinTableStr, " WHERE ",
       ObjIdField, "=", get_id_str(Obj), " AND ",
       OtherIdField, "=", get_id_str(OtherObj)]).

get_many_to_many_query_strs(JoinTable, Obj, OtherObj) ->
    {atom_to_list(JoinTable), get_module_str(Obj) ++ "_id",
     get_module_str(OtherObj) ++ "_id"}.

verify_saved(Obj) ->
    case get_is_new(Obj) of
	true ->
	    {error, no_such_record};
	false ->
	    ok
    end.

verify_saved(Obj1, Obj2) ->
    case verify_saved(Obj1) of
	ok ->
	    case verify_saved(Obj2) of
		ok ->
		    ok;
		Err ->
		    Err
	    end;	    
	Err ->
	    Err
    end.

%% Utility functions

%% Useful for encoding a list of variables in a SQL statement.
prepend_list(Elems, Str) ->
    {Res, _} =
	lists:foldl(
	  fun(Val, {Str1, false}) ->
		  {Val ++ Str1, true};
	     (Val, {Str1, true}) ->
		  {Val ++ [$, | Str1], true}
	  end, {Str, false}, lists:reverse(Elems)),
    Res.

%% Encode a value prior to inserting it in a SQL statement.
encode(undefined) ->
    "null";
encode(Val) when is_atom(Val) ->
    atom_to_list(Val);
encode(Val) when is_list(Val) ->
    mysql:quote(Val);
encode(Val) when is_integer(Val) ->
    integer_to_list(Val);
encode(Val) when is_float(Val) ->
    [Res] = io_lib:format("~.6f", [Val]), % TODO what precision to use?
    Res;
encode({{Year, Month, Day}, {Hour, Minute, Second}}) ->
    Res = io_lib:format("'~B-~B-~B ~B:~B:~B'", [Year, Month, Day, Hour,
					  Minute, Second]),
    lists:flatten(Res);  % TODO is lists:flatten too slow?
encode({Time1, Time2, Time3}) -> %% TODO is this format always valid??
    Res = io_lib:format("'~B-~B-~B'", [Time1, Time2, Time3]),
    lists:flatten(Res);
encode(Val) ->
    Val.
